package com.wy.util;

import com.wy.vo.SpareRoom;
import com.wy.vo.UseRoom;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Date;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by wy on 2017/12/5.
 */
public class GxtcSpareRoomImport {
    private String getRoomSql = "SELECT CASE WHEN A .xqmc LIKE '%长岗校区%' THEN 2 WHEN A .xqmc LIKE '%五合校区%' THEN 4 WHEN A .xqmc LIKE '%明秀校区%' THEN 1 WHEN A .xqmc LIKE '%明阳校区%' \n" +
            "THEN 3 END AS divisionid,SUBSTR (A .xqmc, 0, 4) AS divisionname,A .buildingid AS buildingid,A .buildingname AS buildingname,\n" +
            "A .roomname AS roomname,A .hasseat AS hasseat,A .examseat AS examseat,  A .usedate AS usedate,A .roomtype AS roomtype,178 AS schoolid,A.skxq as SKXQ,\n" +
            "CASE WHEN A . CLASS LIKE '%01%' AND A . CLASS NOT LIKE '%101%' THEN '上课' ELSE '空闲' END AS class1,\n" +
            "CASE WHEN A . CLASS LIKE '%02%' THEN '上课' ELSE '空闲' END AS class2,CASE WHEN A . CLASS LIKE '%03%' THEN '上课' ELSE '空闲' END AS class3,\n" +
            "CASE WHEN A . CLASS LIKE '%04%' THEN '上课' ELSE '空闲' END AS class4,CASE WHEN A . CLASS LIKE '%05%' THEN '上课' ELSE '空闲' END AS class5,\n" +
            "CASE WHEN A . CLASS LIKE '%06%' THEN '上课' ELSE '空闲' END AS class6,CASE WHEN A . CLASS LIKE '%07%' THEN '上课' ELSE '空闲' END AS class7,\n" +
            "CASE WHEN A . CLASS LIKE '%08%' THEN '上课' ELSE '空闲' END AS class8,CASE WHEN A . CLASS LIKE '%09%' THEN '上课' ELSE '空闲' END AS class9,\n" +
            "CASE WHEN A . CLASS LIKE '%10%' AND A . CLASS NOT LIKE '%010%' THEN '上课' ELSE '空闲' END AS class10,\n" +
            "CASE WHEN A . CLASS LIKE '%11%' THEN '上课' ELSE\t'空闲' END AS class11,CASE WHEN A . CLASS LIKE '%12%' THEN '上课' ELSE '空闲' END AS class12 \n" +
            "FROM( \n" +
            "SELECT j.YXZWS AS hasseat,j.KSZWS AS examseat,j.JXLH AS buildingid, H .JXLMC AS buildingname,j.jsh AS roomname,l.xqmc AS XQMC,\n" +
            "b.syrq AS usedate,j.jsyt AS roomtype,SUBSTR (b.syjc, 2, LENGTH(b.SYJC)) AS CLASS ,substr(b.syjc, 1, 1) as skxq\n" +
            "FROM GXJX_JSJBSJZLB j,GXJX_JSSYSJZLB b,GXXX_XQJBSJZL l,JXLH_WZH H WHERE j.jsh = b.jsh AND j.xqh = l.xqh AND j.jxlh = H .jxlh and b.syrq like '2017-2018%'\n" +
            "GROUP BY j.XQH,j.YXZWS,j.KSZWS,j.jxlh,       H .JXLMC,j.jsh,l.xqmc,j.jsyt,b.syrq,SUBSTR (b.syjc, 2, LENGTH(b.SYJC)),substr(b.syjc, 1, 1)ORDER BY USEDATE DESC \n" +
            " ) A ";

    private String insertSql = "INSERT INTO `mobilecampus`.`t_room` ( `roomname`,`xn`, `xq`,`schoolid`,`usedate`,`divisionname`,`divisionid`,'buildingid',`class`)"
            + " VALUES" + "(" + "'{roomname}'," +  "'{xn}'," + "'{xq}'," +"'178', " + "'{usedate}'," + ");";

    private Connection getConn(){
        Connection conn;
        String className;
        String url;
        String username;
        String password;

        InputStream in = GxtcSpareRoomImport.class.getClassLoader().getResourceAsStream("config.properties");
        Properties pro = new Properties();
        try {
            pro.load(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
        conn = null;
        className = pro.getProperty("className");
        url = pro.getProperty("url");
        username = pro.getProperty("username");
        password = pro.getProperty("password");
        try {
            Class.forName(className);
            try {
                conn = DriverManager.getConnection(url, username, password);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        System.out.println("连接成功");
        try {
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return conn;
    }
    //从mysql获取校历,key为xq_zc_  例如 1_2_ 表示第1学期第2周
    public Map<String, List<java.util.Date>> getSchoolTime() {
        String sql = "SELECT * FROM t_schooltime WHERE xn='2017-2018' and type=1 and schoolid=178";
        String username = "root";
        String pwd = "gxtc2016";
        String url = "jdbc:mysql://172.16.12.193:3306/mobilecampus";
        PreparedStatement pst = null;
        Connection conn = null;
        ResultSet rs = null;
        Map<String, List<java.util.Date>> map = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url,username,pwd);
        map = new HashMap<>();
        try{
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                int xq = rs.getInt("xq");
                int type = rs.getInt("type");
                int xlzc = rs.getInt("zc");
                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String starttime = rs.getString("starttime");
                java.util.Date startDate=formatter.parse(starttime);
                if (type == 1) {
                    String xqzc = "xq_" + xq + "_" + "zc_" +xlzc;
                    List<java.util.Date> date = new ArrayList<>();
                    Calendar calendar = Calendar.getInstance();
                    // System.out.println(zc);
                    for (int i = 0; i < 7; i++) {
                        calendar.setTime(startDate);
                        calendar.add(Calendar.DAY_OF_MONTH, i);
                        date.add(calendar.getTime());
                        // System.out.println(calendar.getTime());
                    }
                    map.put(xqzc, date);
                }

            }

        }catch(Exception e){
            System.out.println("date is error");
        }
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
            try {
                rs.close();
                pst.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        return map;
    }

    //从源数据库获取教室使用情况
    private List<UseRoom> getRoom(Connection conn){
        List<UseRoom> list = new ArrayList<>();
        PreparedStatement ps;
        ResultSet rs;
        UseRoom useRoom;
        String clazz[] = new String[13];
        try {
            ps = conn.prepareStatement(getRoomSql);
            rs = ps.executeQuery();
            while (rs.next()){
                useRoom = new UseRoom();
                useRoom.setBuildingId(rs.getInt("BUILDINGID"));
                useRoom.setBuildingName(rs.getString("BUILDINGNAME"));
                useRoom.setDivisionId(rs.getInt("DIVISIONID"));
                useRoom.setDivisionName(rs.getString("divisionname"));
                useRoom.setExamSet(rs.getInt("EXAMSEAT"));
                useRoom.setHasSeat(rs.getInt("HASSEAT"));
                useRoom.setRoomName(rs.getString("ROOMNAME"));
                useRoom.setRoomType(rs.getString("ROOMTYPE"));
                useRoom.setUseDate(rs.getString("USEDATE"));
                useRoom.setSchoolId(rs.getInt("SCHOOLID"));
                useRoom.setSkxq(rs.getString("SKXQ"));
                clazz[1] = rs.getString("CLASS1");
                clazz[2] = rs.getString("CLASS2");
                clazz[3] = rs.getString("CLASS3");
                clazz[4] = rs.getString("CLASS4");
                clazz[5] = rs.getString("CLASS5");
                clazz[6] = rs.getString("CLASS6");
                clazz[7] = rs.getString("CLASS7");
                clazz[8] = rs.getString("CLASS8");
                clazz[9] = rs.getString("CLASS9");
                clazz[10] = rs.getString("CLASS10");
                clazz[11] = rs.getString("CLASS11");
                clazz[12] = rs.getString("CLASS12");
                useRoom.setClazz(clazz);
                list.add(useRoom);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }


    private List<SpareRoom> setRoom(List useRooms, Map<String,List<Date>> schoolTime){
        // buildingName+roomname+usedate 表示一条key
        Map<String, SpareRoom> map = new HashMap();
        List spareRooms = new ArrayList();//空闲教室
        SpareRoom spareRoom;
        for (String xq_zc : schoolTime.keySet()) {//遍历每学期的每一周
            String xq = xq_zc.split("_")[1];
            String zc = xq_zc.split("_")[3];
            List<Date> dates = schoolTime.get("xq_" + xq + "_" + "zc_" + zc);//获取某一周的七天日期
            for (int d = 1; d <= 7; d++) {//遍历一周7天 某学期某周
                /*********获得日期字符串***********/
                Date date =dates.get(d - 1);
                String strFormat = "yyyy-MM-dd";
                String useDate = "";
                SimpleDateFormat dateFormat = new SimpleDateFormat(strFormat);
                if (date != null) {
                    useDate = dateFormat.format(date);
                }
                /*******************/
                for (int i = 0; i < useRooms.size(); i++) {  //遍历每一条教室使用数据
                    UseRoom useRoom = (UseRoom) useRooms.get(i);
                    String u_xq = useRoom.getUseDate().split("-")[2];
                    String u_skxq = useRoom.getSkxq();
                    if (!u_xq.equals(xq)){//假如学期不对应
                        continue;
                    }
                    if (useRoom.getUseDate().split("-").length == 4) {
                        if (!useRoom.getUseDate().split("-")[3].equals(zc)){//如果上课周次不对应
                            continue;
                        }
                        spareRoom = convertFromUserRoom(useRoom,useDate);
                        spareRooms.add(spareRoom);
                    }
                    else if (useRoom.getUseDate().split("-").length == 5){
                        String startZC = useRoom.getUseDate().split("-")[3];
                        String endZC = useRoom.getUseDate().split("-")[4];
                        if (zc.compareTo(startZC) < 0 || zc.compareTo(endZC) > 0){//如果上课周次不对应
                            continue;
                        }
                        spareRoom = convertFromUserRoom(useRoom, useDate);
                        spareRooms.add(spareRoom);
                    }
                }

            }
        }
        return spareRooms;
    }

    //sql语句写入文件
    private void writeToSql(List<SpareRoom> spareRooms){
        StringBuilder temp = null;
        FileWriter fw = null;
        File file;
        try{
            file = new File("广西师范空闲教室导入脚本new.sql");
            //System.out.println(file.getAbsolutePath());
            fw = new FileWriter(file);
            temp = new StringBuilder();
            for (SpareRoom spareRoom : spareRooms){
                temp.append(roomInsertSqlProudce(spareRoom));
            }
            fw.append(temp);
        }catch (IOException e){
            e.printStackTrace();
        }finally {
            try {
                fw.flush();
                fw.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        System.out.println("sql长度："+temp.length());
    }
    //转换成sql语句
    private String roomInsertSqlProudce(SpareRoom spareRoom) {
        StringBuilder insertSql = new StringBuilder();
        insertSql.append("INSERT INTO mobilecampus.t_room (divisionid,divisionname,buildingid,buildingname,roomname,hasseat,examseat,usedate,roomtype,schoolid");
        for(int i=1;i<13;i++) {
            insertSql.append(",class"+i);
        }
        insertSql.append(String.format(") VALUES(%d,'%s',%d,'%s','%s',%d,%d,'%s','%s',178",spareRoom.getDivisionId(),spareRoom.getDivisionName(),spareRoom.getBuildingId(),
                spareRoom.getBuildingName(),spareRoom.getRoomName(),spareRoom.getHasSeat(),spareRoom.getExamSet(),spareRoom.getUseDate(),
                spareRoom.getRoomType() == null ? " " : spareRoom.getRoomType() ));

        for(int i=1;i<=12;i++) {
            insertSql.append(String.format(",'%s'",spareRoom.getClazz()[i]==null?"":spareRoom.getClazz()[i]));
        }
        insertSql.append(");\n");
        return insertSql.toString();
    }


    //将useRoom转为spareRoom
    private SpareRoom convertFromUserRoom(UseRoom useRoom, String date){
        SpareRoom spareRoom = new SpareRoom();
        spareRoom.setClazz(useRoom.getClazz());
        spareRoom.setSchoolId(useRoom.getSchoolId());
        spareRoom.setUseDate(date);
        spareRoom.setRoomType(useRoom.getRoomType());
        spareRoom.setHasSeat(useRoom.getHasSeat());
        spareRoom.setExamSet(useRoom.getExamSet());
        spareRoom.setRoomName(useRoom.getRoomName());
        spareRoom.setDivisionName(useRoom.getDivisionName());
        spareRoom.setDivisionId(useRoom.getDivisionId());
        spareRoom.setBuildingName(useRoom.getBuildingName());
        spareRoom.setBuildingId(useRoom.getBuildingId());
        return spareRoom;
    }



    public static void main(String[] args) {
        GxtcSpareRoomImport gx = new GxtcSpareRoomImport();
        Connection conn = gx.getConn();
        List useRooms = gx.getRoom(conn);
        Map<String,List<java.util.Date>> dates = gx.getSchoolTime();
        List<SpareRoom> spareRooms = gx.setRoom(useRooms, dates);
        gx.writeToSql(spareRooms);

        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }



}
